CREATE procedure sp_asi_TreatProspectImport @Prefix varchar(5),@ListCode varchar(20),@CurrentLocalDate varchar (50)=''
   as
   set nocount on
   declare
   @ProspectKey uniqueidentifier,
   @ProspectID varchar(15),
   @NewProspectID int,
   @Result int
   declare Get_Prospect cursor for
   select ProspectKey from Prospect where ImportDate is null
   open Get_Prospect
   fetch next from Get_Prospect into @ProspectKey
   WHILE @@FETCH_STATUS = 0
    BEGIN
     exec @Result=sp_iboGetCounter 'Prospect',1,@NewProspectID output
     update Prospect set ListCode=@ListCode,ProspectID=@Prefix+convert(varchar(10),@NewProspectID),
     ImportDate=@CurrentLocalDate,
     LastFirst=
     CASE
     WHEN rtrim(isnull(LastName,''))<>'' and rtrim(isnull(FirstName,''))<>''
     THEN substring(upper(LastName)+', '+upper(FirstName),1,30)
     WHEN rtrim(isnull(LastName,''))='' and rtrim(isnull(FirstName,''))='' and rtrim(isnull(Organization,''))=''
     THEN 'NODATA'
     ELSE ''
     END,
     CompanySort=
     CASE
     WHEN rtrim(isnull(Organization,''))<>'' and substring(upper(isnull(Organization,'')),1,4)='THE '
     THEN substring(upper(Organization),5,30)
     WHEN rtrim(isnull(Organization,''))<>'' and substring(upper(isnull(Organization,'')),1,4)<>'THE '
     THEN  substring(upper(Organization),1,30)
     ELSE ''
     END
     where ProspectKey=@ProspectKey
     fetch next from Get_Prospect into @ProspectKey
    END
   close Get_Prospect
   deallocate Get_Prospect
   delete Prospect where LastFirst='NODATA'

GO
GRANT EXECUTE ON  [dbo].[sp_asi_TreatProspectImport] TO [IMIS]
GO